Gail Shaw wrote:

> Sorry, that cam out a little snarkier than I intended.
> 
> Taking SQL Server as an example (so I can give specific numbers), if an
> index does not contain all the columns required, the break point for use the
> index or scan the table is around 1% of the table. If the index does contain
> all the columns, then the index will be used whether 1 row is required or
> the entire table.
> 
> It's not hard to demonstrate.

Ah, right.

I was thinking of something like "you have a bunch of invoices, indexed 
by invoice number. You want to do something to all the invoices who's 
numbers are less than 999,999,999". In which case, after you've found 
the index nodes that tell you where those table rows are, you still need 
to seek to them and load them. If you're processing a high enough 
percentage of the rows in the table, just sequentially scanning the 
table is going to wind up faster because sequential I/O is faster, and 
reading only the table rather than table + index is less data to 
transfer too.

I hadn't considered the possibility of the entire table data being in 
the index itself. That would move the tipping point somewhat...

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message